package com.example.xyxwebbackend001.mapper;

import com.alibaba.nacos.shaded.org.checkerframework.checker.nullness.qual.NonNull;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.xyxwebbackend001.handler.JsonToListTypeHandler;
import com.example.xyxwebbackend001.model.DTO.PostStatisticsDTO;
import com.example.xyxwebbackend001.model.DTO.TPostsDTO;
import com.example.xyxwebbackend001.model.entity.TPosts;
import org.apache.ibatis.annotations.*;

import java.time.LocalDateTime;
import java.util.List;
import java.util.Map;

@Mapper
public interface TPostsxMapper extends BaseMapper<TPosts> {
//    查询帖子列表
//@Select("""
//        SELECT
//            p.t_post_id,
//            u.user_id,
//            u.avatar_path,
//            u.username,
//            p.title,
//            p.content,
//            p.image_path,
//            p.created_at,
//            COUNT(DISTINCT l.t_like_id) AS likes_count,
//            COUNT(DISTINCT f.t_favorite_id) AS favorites_count,
//            IFNULL(c.comments, JSON_ARRAY()) AS comments,
//            MAX(CASE WHEN l.user_id = #{currentUserId} THEN 1 ELSE 0 END) AS liked_by_current_user,
//            MAX(CASE WHEN f.user_id = #{currentUserId} THEN 1 ELSE 0 END) AS favorited_by_current_user
//        FROM
//            t_posts p
//        LEFT JOIN t_likes l ON p.t_post_id = l.t_post_id
//        LEFT JOIN t_favorites f ON p.t_post_id = f.t_post_id
//        LEFT JOIN user u ON p.user_id = u.user_id
//        LEFT JOIN (
//            SELECT
//                c.t_post_id,
//                COALESCE(
//                    JSON_ARRAYAGG(
//                        JSON_OBJECT(
//                            'commentId', c.t_comment_id,
//                            'username', cu.username,
//                            'content', c.content,
//                            'createdAt', DATE_FORMAT(c.t_created_at, '%Y-%m-%d %H:%i:%s')
//                        )
//                    ),
//                    JSON_ARRAY()
//                ) AS comments
//            FROM t_comments c
//            LEFT JOIN user cu ON c.user_id = cu.user_id
//            GROUP BY c.t_post_id
//        ) c ON p.t_post_id = c.t_post_id
//        WHERE
//            p.image_path IS NOT NULL
//            AND p.image_path != ''
//            AND p.title IS NOT NULL
//            AND p.title != ''
//            AND p.content IS NOT NULL
//            AND p.content != ''
//            AND u.user_id IS NOT NULL
//            AND p.status = 1
//        GROUP BY p.t_post_id
//        """)
//@Results({
//        @Result(property = "postId", column = "t_post_id"),
//        @Result(property = "comments", column = "comments", typeHandler = JsonToListTypeHandler.class)
//})
List<TPostsDTO> getTPostsList(
        @Param("currentUserId") Integer currentUserId,
        @Param("ew") @NonNull QueryWrapper<TPosts> wrapper
);

//查询用户所有帖子id
@Select("""
    SELECT t_post_id FROM t_posts WHERE user_id = #{userId}
""")
List<Integer> selectPostIdsByUserId(@Param("userId") Integer userId);

    @Select("SELECT * FROM t_posts WHERE t_post_id = #{tPostId}")
    TPosts selectByTPostId(@Param("tPostId") Integer tPostId);

    @Select("SELECT image_path FROM t_posts WHERE t_post_id = #{tPostId}")
    String getFileNameByPostId(@Param("tPostId") Integer tPostId);


    @Update("UPDATE t_posts SET image_path = #{imagePath}, updated_at = NOW() WHERE t_post_id = #{tPostId}")
    int updateImagePath(@Param("tPostId") Integer tPostId, @Param("imagePath") String imagePath);

    @Update("UPDATE t_posts SET " +
            "title = #{title}, " +
            "content = #{content}, " +
            "image_path = #{imagePath}, " +
            "place = #{place}, " +
            "tags = #{tags} " +
            "WHERE t_post_id = #{tPostId}")
    int updatePost(
            @Param("tPostId") Integer tPostId,
            @Param("title") String title,
            @Param("content") String content,
            @Param("imagePath") String imagePath,
            @Param("place") String place,
            @Param("tags") String tags
    );

    @Select("SELECT * from t_posts WHERE user_id = #{userId} AND status = 1 AND title IS NOT NULL AND title != ''")
    List<TPosts> getPostsListByUserId(@Param("userId") Integer userId);

    @Update("UPDATE t_posts SET status = 0 WHERE t_post_id = #{tPostId}")
    int deletePostById(@Param("tPostId") Integer tPostId);

    @Select("SELECT DATE(created_at) as date, COUNT(*) as count " +
            "FROM t_posts " +
            "WHERE created_at >= #{startDate} " +
            "GROUP BY DATE(created_at) " +
            "ORDER BY date DESC")
    List<PostStatisticsDTO> selectRecentPostCounts(@Param("startDate") LocalDateTime startDate);

    @Select("""
    SELECT 
        t.name AS name,
        COUNT(p.t_post_id) AS value 
    FROM (${enumValues}) t
    LEFT JOIN t_posts p 
        ON FIND_IN_SET(t.code, p.tags) > 0
    GROUP BY t.code, t.name
    ORDER BY value DESC
""")
    List<Map<String, Object>> selectTagStatistics(@Param("enumValues") String enumValues);

    @Select("""
    SELECT 
        CONCAT(c.city_name, '市') AS name,
        COUNT(t.t_post_id) AS value 
    FROM city c
    LEFT JOIN t_posts t 
        ON c.city_id = t.place 
        AND t.status = 1
    GROUP BY c.city_id, c.city_name
    ORDER BY value DESC
""")
    List<Map<String, Object>> selectCityStats();
}
